/*!
基于sqlite的文件记录
```sql
CREATE TABLE stat(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name        TEXT    NOT NULL,
        fhash       CHAR(2)  NOT NULL,
        status    	TINYINT UNSIGNED NOT NULL DEFAULT 0,
        ctime   DATETIME
     );
CREATE INDEX idx_fhash ON stat(fhash);
```
*/

use anyhow::{anyhow, Ok};
use serde::{Deserialize, Serialize};
use sqlx::sqlite::SqlitePool;
use sqlx::{migrate::MigrateDatabase, Sqlite};
use std::fmt::Debug;

#[cfg(feature = "tracelog")]
use tracing::error;
#[cfg(feature = "rotatelog")]
use log::{debug, error, info, warn};

/**
维护SqlitePool连接池
 */
pub struct Gfstat {
    pub db: SqlitePool,
}
impl Gfstat {
    pub async fn new(root: &str) -> Self {
        let mut db_init = false;
        let gfs_db = format!("sqlite:{}/gfs_stat.db", root);
        if !Sqlite::database_exists(gfs_db.as_str())
            .await
            .unwrap_or(false)
        {
            if let Err(err) = Sqlite::create_database(gfs_db.as_str()).await {
                panic!("error: {}", err);
            } else {
                db_init = true;
            }
        }

        let pool = SqlitePool::connect(gfs_db.as_str()).await.expect("why?");
        if db_init {
            sqlite_db_init(&pool).await.expect("why");
        }
        return Gfstat { db: pool };
    }

    /**
    gfs Node中slave添加新文件记录
     - 区别master仅在于，master的id是系统自增的，添加时不进行指定；
     - slave的id是指定的，值来自于master的stat
     */
    pub async fn slave_add(
        &self,
        id: i32,
        name: &str,
        ihash: i32,
        status: u8,
    ) -> anyhow::Result<i32> {
        let lt = chrono::Local::now();
        let ct = chrono::NaiveDateTime::from_timestamp_millis(
            lt.timestamp_millis() + 8 * 60 * 60 * 1000,
        )
        .expect("why");
        let cid =
            sqlx::query(r#"INSERT INTO stat(id, name, fhash, status, ctime) VALUES (?,?,?,?,?)"#)
                .bind(id)
                .bind(name)
                .bind(format!("{:02x}", ihash).as_str())
                .bind(status)
                .bind(ct)
                .execute(&self.db)
                .await?
                .last_insert_rowid();
        Ok(cid as i32)
    }
    /**
    gfs Node中master添加新文件记录
     - 区别slave在于，master的id是系统自增的，添加时不进行指定；
     - slave的id是指定的，值来自于master的stat
     */
    pub async fn master_add(&self, ihash: i32, name: &str) -> anyhow::Result<i32> {
        let lt = chrono::Local::now();
        let ct = chrono::NaiveDateTime::from_timestamp_millis(
            lt.timestamp_millis() + 8 * 60 * 60 * 1000,
        )
        .expect("why");
        let cid = sqlx::query(r#"INSERT INTO stat(name,fhash, ctime) VALUES (?,?,?)"#)
            .bind(name)
            .bind(format!("{:02x}", ihash).as_str())
            .bind(ct)
            .execute(&self.db)
            .await?
            .last_insert_rowid();
        Ok(cid as i32)
    }
    /**
    获取当前系统中的最大id记录
     - 如果最大记录被删除，会怎么样？
     - 如此导致新增失败后会是什么后果？

    */
    pub async fn get_last(&self, limit: u32) -> anyhow::Result<Vec<Stat>> {
        if limit < 1 {
            return Err(anyhow!("limit must > 0"));
        }
        let sql = "SELECT id, name, fhash, status FROM stat order by id desc limit ?";
        let row = sqlx::query_as::<_, Stat>(sql)
            .bind(limit)
            .fetch_all(&self.db)
            .await?;
        return Ok(row);
    }

    /**
    获取当前系统中的最大id值
     - 如果最大记录被删除会怎么样？
     - 如此导致新增失败后会是什么后果？
     - 是否读取sqlite_sequence中的记录代替更好？
    */
    pub async fn get_last_id_old(&self) -> i32 {
        if let anyhow::Result::Ok(row) = self.get_last(1).await {
            if row.len() > 0 {
                row[row.len() - 1].id
            } else {
                0
            }
        } else {
            0
        }
    }

    /**
    获取当前系统中的最大id值
     - 读取sqlite_sequence中的自增序列号
    */    
    pub async fn get_last_id(&self) -> i32 {
        let sql = "SELECT name, seq FROM sqlite_sequence WHERE name='stat'";
        let rows = sqlx::query_as::<_, Sequence>(sql).fetch_all(&self.db).await;
        match rows {
            std::result::Result::Ok(rows) => {
                if rows.len() == 1 {
                    return rows[0].seq as i32;
                }
            }
            std::result::Result::Err(err) => {
                error!("{:#?}", err);
            }
        }
        error!("read seq from sqlite_sequence, but null");
        0
    }

    /**
    获取当前系统中的从某id开始的limit条数据
    */
    pub async fn get_next_from_id(&self, id: i32, limit: u32) -> anyhow::Result<Vec<Stat>> {
        let sql = "SELECT id, name, fhash, status FROM stat WHERE id > ? order by id asc limit ?";
        let rows = sqlx::query_as::<_, Stat>(sql)
            .bind(id)
            .bind(limit)
            .fetch_all(&self.db)
            .await?;
        return Ok(rows);
    }
    /**
    获取当前系统中指定fhash下的文件列表，按id排序，limit限量
     - fhash已经做了index
    */
    pub async fn get_dir(&self, hash: &str, id: i32, limit: u32) -> anyhow::Result<Vec<Stat>> {
        let sql = "SELECT id, name, fhash, status FROM stat WHERE fhash = ? AND id > ? order by id asc limit ?";
        let rows = sqlx::query_as::<_, Stat>(sql)
            .bind(hash)
            .bind(id)
            .bind(limit)
            .fetch_all(&self.db)
            .await?;
        return Ok(rows);
    }

    /**
    获取当前系统中指定fhash下的所有文件列表（可能会爆！！！）
     - fhash已经做了index
    */
    pub async fn get_hash_files(&self, hash: &str) -> anyhow::Result<Vec<Stat>> {
        let sql = "SELECT id, name, fhash, status FROM stat WHERE fhash = ? order by id asc";
        let rows = sqlx::query_as::<_, Stat>(sql)
            .bind(hash)
            .fetch_all(&self.db)
            .await?;
        return Ok(rows);
    }

    /**
    获取当前系统中指定fhash和文件名的文件列表，按id排序，limit限量
     - fhash已经做了index
     - name没有做index，可能会损耗性能
    */
    pub async fn get_by_name(
        &self,
        hash: &str,
        fname: &str,
        id: i32,
        limit: u32,
    ) -> anyhow::Result<Vec<Stat>> {
        let sql = "SELECT id, name, fhash, status FROM stat WHERE fhash = ? AND name= ? AND id > ? order by id asc limit ?";
        let rows = sqlx::query_as::<_, Stat>(sql)
            .bind(hash)
            .bind(fname)
            .bind(id)
            .bind(limit)
            .fetch_all(&self.db)
            .await?;
        return Ok(rows);
    }
    /**
    获取当前系统中的文件统计现状，
     - select fhash, count(id) from stat group by fhash;  
     - 尝试用元数据作为行对象，let rows = sqlx::query_as::<_, (String,i32)>(sql)            
    */
    pub async fn get_status(&self) -> anyhow::Result<Vec<(String,i32)>> {
        let sql = "SELECT fhash, COUNT(id) FROM stat GROUP BY fhash";
        let rows = sqlx::query_as::<_, (String,i32)>(sql)            
            .fetch_all(&self.db)
            .await?;
        return Ok(rows);
    }

}
/// 文件信息数据库存储结构
#[derive(Debug, sqlx::FromRow, Clone, Serialize, Deserialize)]
// #[sqlx(rename_all = "PascalCase")]
pub struct Stat {
    pub id: i32,
    pub name: String,
    pub fhash: String,
    //  ctime: chrono::NaiveDateTime,
    status: u8,
}

/// sqlite3的系统自增序列表结构， sqlite_sequence(name, seq)
#[derive(Debug, sqlx::FromRow, Clone, Serialize, Deserialize)]
// #[sqlx(rename_all = "PascalCase")]
pub struct Sequence {
    pub name: String,
    pub seq: u32,
}

/**
stat数据库的初始化
 */
async fn sqlite_db_init(pool: &SqlitePool) -> anyhow::Result<()> {
    let sql = r#"
    CREATE TABLE stat(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name        TEXT    NOT NULL,
        fhash       CHAR(2)  NOT NULL,
        status    	TINYINT UNSIGNED NOT NULL DEFAULT 0, 
        ctime   DATETIME
     );    
    "#;
    /*
       let slave_sql = r#"
       CREATE TABLE stat(
           id INTEGER  PRIMARY KEY NOT NULL,
           name        TEXT    NOT NULL,
           fhash       CHAR(2)  NOT NULL,
           status    	INTEGER NOT NULL DEFAULT 0,
           ctime   DATETIME
        );
       "#;
    */
    let affect_rows = sqlx::query(sql).execute(pool).await?;
    /*
    } else {
        dbg!(&slave_sql);
        sqlx::query(slave_sql).execute(pool).await?
    }; */
    println!("{:?}", affect_rows);
    let idx_sql = r#"CREATE INDEX idx_fhash ON stat(fhash);"#;
    let affect_rows = sqlx::query(idx_sql).execute(pool).await?;
    println!("create index rows = {:#?}", affect_rows);

    Ok(())
}
/*
async fn add_gfs_master_stat() {
    let gfss = Gfstat::new(true, "").await;
    let id = gfss.master_add("00", "helloworld.docx").await.unwrap();
    println!("{}", id);
}

async fn get_gfs_master_last() {
    debug!("last");
    let gfss = Gfstat::new(true, "").await;
    let stat = gfss.get_last(1).await.unwrap();
    println!("{:?}", stat);
}

async fn get_gfs_master_next() {
    debug!("next");
    let gfss = Gfstat::new(true, "").await;
    let svs = gfss.get_next_by_id(0, 20).await.unwrap();
    for s in svs.iter() {
        println!("{:#?}", s);
    }
}
*/
/*
async fn add_gfs_slave_stat() {
    let gfss = Gfstat::new(false, "").await;
    let lt = chrono::Local::now();
    let ct =
        chrono::NaiveDateTime::from_timestamp_millis(lt.timestamp_millis() + 8 * 60 * 60 * 1000)
            .expect("why");

    let stat = Stat {
        id: 1,
        name: "name.txt".to_string(),
        fhash: "a1".to_string(),
        status: 0,
    };
    let id = gfss.slave_add(&stat).await.unwrap();
    println!("{}", id);
}

async fn get_gfs_slave_last() {
    debug!("last");
    let gfss = Gfstat::new(false, "").await;
    let stat = gfss.get_last(1).await.unwrap();
    println!("{:?}", stat);
}

async fn get_gfs_slave_next() {
    debug!("next");
    let gfss = Gfstat::new(false, "").await;
    let svs = gfss.get_next_by_id(0, 20).await.unwrap();
    for s in svs.iter() {
        println!("{:#?}", s);
    }
}

#[test]
fn gfs_new_stat_test() {
    let f = add_gfs_master_stat();
    let rt = tokio::runtime::Runtime::new().unwrap();
    rt.block_on(f);
}

#[test]
fn gfs_last_stat_test() {
    let f = get_gfs_master_last();
    let rt = tokio::runtime::Runtime::new().unwrap();
    rt.block_on(f);
}

#[test]
fn gfs_next_stat_test() {
    let f = get_gfs_master_next();
    let rt = tokio::runtime::Runtime::new().unwrap();
    rt.block_on(f);
}

#[test]
fn gfs_last_slave_stat_test() {
    let f = get_gfs_slave_last();
    let rt = tokio::runtime::Runtime::new().unwrap();
    rt.block_on(f);
}

#[test]
fn gfs_next_slave_stat_test() {
    let f = get_gfs_slave_next();
    let rt = tokio::runtime::Runtime::new().unwrap();
    rt.block_on(f);
}
*/
